


set more off

local file_path_in "/tochange/"

local file_path_code "/tochange/"

insheet using "`file_path_in'/Compustat/compustat_aq.csv", clear

rename fyear year
gen seller = conm

** clean annual variables
rename act curr_assets_a
rename at assets_a
rename ch cash_a
rename che cash_stequiv_a
rename dclo debt_cap_lease_a
rename dcs debt_consol_sub_a
rename dd1 ltdebt_due1yr_a
rename dd2 ltdebt_due2yr_a
rename dd3 ltdebt_due3yr_a
rename dd4 ltdebt_due4yr_a
rename dd5 ltdebt_due5yr_a
rename dfs debt_fin_sub_a
rename dlc debt_curr_liab_a
rename dltt ltdebt_total_a
rename dm mortgagedebt_secured_a
rename dn debt_notes_a
rename ds debt_subord_a
rename dt total_debt_a
rename dltis ltdebt_issue_a
rename esub equity_unconsolsub_a
rename dxd2 debtxclcap_due2yr_a
rename dxd3 debtxclcap_due3yr_a
rename dxd4 debtxclcap_due4yr_a
rename dxd5 debtxclcap_due5yr_a
rename hbbck backlog_tot_a   
rename hbbckav backlog_avgpr_a  
rename hbbcku backlog_units_a
rename hbcs communities_a 
rename hbdc deliveries_a  
rename hbdcav deliveries_avgpr_a
rename hbdcu deliveries_units_a
rename hbinvfh finhomes_constr_a  
rename hbinvlud land_under_dev_a
rename hbinvno invent_notowned_a 
rename hbinvt invent_total_a  
rename hbinvuo undev_invent_own_a  
rename hblo lots_owned_a
rename hbluo lots_option_a
rename hbnoc new_contracts_a  
rename hbnocav new_contracts_avgpr_a
rename hbnocu new_contracts_units_a
rename sale revt_a
rename xsga sga_a
rename cogs cogs_a
rename wcap wkg_cap_a
rename re retain_earn_a
rename ceq common_eq_a
rename seq shareholer_eq_a
rename prccq price_close_a
gen prccq = price_close_a
rename csho common_shares_out_a
rename lt liab_a  
rename lct curr_liab_a 
rename ni net_income_a

replace cash_a = . if cash_a==0

do "`file_path_code'/clean-builder-names.do" "dealscan"
do "`file_path_code'/trim-builder-names.do" "dealscan"

replace fqtr = 2 if gvkey==25082 & datadate==20000630
replace fqtr = 3 if gvkey==25082 & datadate==20000930
replace fqtr = 4 if gvkey==25082 & datadate==20001231
replace fqtr = 2 if gvkey==25082 & datadate==20010331
replace year = 2001 if gvkey==25082 & datadate==20010331

rename fqtr quarter
gen month = 3 if quarter==1
replace month = 6 if quarter==2
replace month = 9 if quarter==3
replace month = 12 if quarter==4
gen qdate = qofd(mdy(month,1,year))
format qdate %tq

encode tic, gen(tic1)
tsset tic1 qdate
	
** create ratio vars
gen mtb_q = (cshoq*prccq)/ceqq
gen profitability_a = net_income_a / assets_a
gen fixed_a = (assets_a-intan) / assets_a

gen pctoption_a = lots_option_a/lots_owned_a
gen Ldeliveries_units_a = L.deliveries_units_a
gen Lbacklog_units_a = L2.backlog_units_a
gen cancel_a = Lbacklog_units / deliveries_units_a
gen pctsecured_a = mortgagedebt_secured_a / ltdebt_total_a
gen pctbond_a = debt_notes_a / ltdebt_total_a
gen pctdev_a = land_under_dev_a / assets_a
gen due1yr_a = ltdebt_due1yr_a
gen due1yrltdebt_a = ltdebt_due1yr_a / ltdebt_total_a if !mi(ltdebt_due1yr_a) & !mi(ltdebt_total_a)
gen due1yrcash_a = ltdebt_due1yr_a / cash_a 
gen due1yrassets_a = ltdebt_due1yr_a / assets_a
gen due0yr_a = L.ltdebt_due1yr_a
gen due0yrltdebt_a = due0yr_a / ltdebt_total_a if !mi(due0yr_a) & !mi(ltdebt_total_a)
gen due0yrcash_a = due0yr_a / cash_a 
gen lndue0yr_a = ln(due0yr_a)
gen due0yrassets_a = due0yr_a / assets_a
gen pctcash_a = cash_a / assets_a
gen lndue1yr_a =  ln(ltdebt_due1yr_a)
gen lneq_subs_a = ln(equity_unconsolsub_a)
gen pcteq_subsassets_a = equity_unconsolsub_a / assets_a
gen pcteq_subcash_a = equity_unconsolsub_a / cash_a
gen inv_sales_a = invent_total_a / revt_a 

*************** quarterly variables *****************
rename dlttq total_debt_q  
rename atq assets_q
rename dpy depr_q
rename ppentq ppe_q
rename teqq equity_q
rename dvy dividends_q
rename cheq cash_q
rename cogsq cogs_q
rename xsgaq sga_q
rename revtq revt_q
rename xinty xint_a
rename xintq xint_q
rename actq curr_assets_q
rename wcapchy wkg_cap_chg_q
rename req retain_earn_q
rename ceqq common_eq_q
rename seqq shareholder_eq_q
rename prccq price_close_q
rename cshoq common_shares_out_q
rename ltq liab_q
rename lctq curr_liab_q
rename dlcq debt_curr_liab_q

****** SAMPLE CONSTRUCTION ****** 

encode seller, gen(builder)
tsset builder qdate
gen Lassets_a = L.assets_a
gen asset_growth = ((assets_a - Lassets_a) / Lassets_a)*100 

** generate treatment var
keep if !mi(assets_a) & !mi(ltdebt_total_a) & (ltdebt_due1yr_a)

** clean up obs 
drop if seller=="ALLSTATE"
replace xint_q = 0 if xint_q<=0.02
replace assets_q = . if seller=="GLOBALDIVERSIFIEDINDS" & inlist(qdate,164,165)
replace mortgagedebt_secured_a = . if seller=="CALPROP"
replace pctsecured_a = . if seller=="CALPROP"
drop if year==2000 | year>=2016

** generate leverage ratio, current leverage ratio, coverage ratio and altmann z-score
local type a q
foreach k of local type {
	gen leverage_`k' = (total_debt_`k')/assets_`k'
	gen currleverage_`k' = (debt_curr_liab_`k'/curr_assets_`k')

	gen ebit_`k' = revt_`k' - cogs_`k' - sga_`k'
	gen coverage_`k' = ebit_`k' / (xint_`k')  
	
	gen working_capital_`k' =  curr_assets_`k' - curr_liab_`k'
	gen mkt_eq_`k' = common_shares_out_`k' * price_close_`k'

	gen A = working_capital_`k' / assets_`k' 
	gen B = retain_earn_`k' / assets_`k'
	gen C = ebit_`k' / assets_`k'
	gen D = mkt_eq_`k' / liab_`k'
	gen E = revt_`k' / assets_`k'
	gen altmann_`k' = 1.2*A + 1.4*B + 3.3*C + 0.6*D + 1.0*E
	drop A B C D E 
}

** replace 0 ratio's to missing
local listvar leverage_a leverage_q coverage_q  pctcash_a currleverage_a  altmann_a altmann_q profitability_a mtb_q fixed_a inv_sales_a
foreach c of local listvar {
	replace `c'=. if `c'==0
}

*************************************************

** create time invariant vars, fixed in 2006, 2002 - ANNUAL
local listvar cash_a assets_a pctbond_a pctsecured_a due1yrltdebt_a due1yrcash_a equity_unconsolsub_a due0yrassets_a due1yrassets_a ///
pcteq_subcash_a pcteq_subsassets_a lndue1yr_a pctcash_a pctdev_a pctoption_a due0yr_a due1yr_a leverage_a altmann_a currleverage_a  ///
 xint_a profitability_a  fixed_a inv_sales_a   ///
revt_a invent_total_a backlog_tot_a backlog_avgpr_a backlog_units_a deliveries_a deliveries_units_a finhomes_constr_a land_under_dev_a undev_invent_own_a invent_notowned_a

foreach c of local listvar {
	local out a
	local date04 "year==2004"
	local date05 "year==2005"
	local date06 "year==2006"
	local date07 "year==2007"
	local date02 "year==2002"
	local date11 "year==2011"
	
	** get variables for each year
	gen temp`c'04 = `c' if `date04'
	bysort seller: egen `c'04 = max(temp`c'04)
	gen temp`c'06 = `c' if `date06'
	bysort seller: egen `c'06 = max(temp`c'06)
	gen temp`c'05 = `c' if `date05'
	bysort seller: egen `c'05 = max(temp`c'05)
	gen temp`c'02 = `c' if `date02'
	bysort seller: egen `c'02 = max(temp`c'02)
	gen temp`c'11 = `c' if `date11'
	bysort seller: egen `c'11 = max(temp`c'11)
	drop temp*
}  

** create time invariant vars, fixed in 2006, 2002
local listvar leverage_q coverage_q altmann_q  xint_q mtb_q revt_q   
foreach c of local listvar {
	
	local out q 
	local date05 "qdate==180"
	local date06 "qdate==184"
	local date02 "qdate==168"
	local date11 "qdate==204"
	local date10 "qdate==200"
	local date09 "qdate==196"

	gen temp`c'06 = `c' if `date06'
	bysort seller: egen `c'06 = max(temp`c'06)
	bysort seller: egen `c'06_avg_2 = mean(`c') if year==2006
	bysort seller: egen `c'06_mean = max(`c'06_avg_2)
	
	gen temp`c'05 = `c' if `date05'
	bysort seller: egen `c'05 = max(temp`c'05)
	bysort seller: egen `c'05_avg_2 = mean(`c') if year==2005
	bysort seller: egen `c'05_mean = max(`c'05_avg_2)
	
	gen temp`c'02 = `c' if `date02'
	bysort seller: egen `c'02 = max(temp`c'02)
	bysort seller: egen `c'02_avg_2 = mean(`c') if year==2002
	bysort seller: egen `c'02_mean = max(`c'02_avg_2)
	
	gen temp`c'11 = `c' if `date11'
	bysort seller: egen `c'11 = max(temp`c'11)
	bysort seller: egen `c'11_avg_2 = mean(`c') if year==2011
	bysort seller: egen `c'11_mean = max(`c'11_avg_2)
	
	gen temp`c'10 = `c' if `date10'
	bysort seller: egen `c'10 = max(temp`c'10)
	bysort seller: egen `c'10_avg_2 = mean(`c') if year==2010
	bysort seller: egen `c'10_mean = max(`c'10_avg_2)
	
	gen temp`c'09 = `c' if `date09'
	bysort seller: egen `c'09 = max(temp`c'09)
	bysort seller: egen `c'09_avg_2 = mean(`c') if year==2009
	bysort seller: egen `c'09_mean = max(`c'09_avg_2)
	
	drop temp* *_avg_2
}  

sort seller qdate
order seller qdate datadate altmann_q ebit_q mkt_eq_q retain_earn_q working_capital_q

label var fixed_a06 "Tangibility_{06}"
label var mtb_q06 "Market to Book_{06}"
label var profitability_a06 "Profitability_{06}"
label var inv_sales_a "Inventory to Sales_{06}"

** annual vars
preserve
	collapse cash_a  leverage_a   coverage_a, by(year)
	local listvar cash_a leverage_a   
	label var leverage_a "leverage"
	label var coverage_a "coverage"
	
	foreach c of local listvar {
		local y `=subinstr("`c'","_a","",.)'
		twoway line `c' year, title("`y'") ytitle("") xtitle("year") graphregion(color(white)) xlabel(2000(2)2015) saving("`file_path_in'/reg output/`c'", replace) 
		graph export "`file_path_in'/reg output/`c'.png", replace 
		graph export "`file_path_in'/reg output/`c'.eps", replace
	}	
	** Leverage ratio for printing
	twoway line leverage_a year, ytitle("Leverage Ratio") xtitle("Year") graphregion(color(white)) xlabel(2000(2) 2015) saving("`file_path_in'/reg output/leverage_a", replace) 
		graph export "`file_path_in'/reg output/leverage_a.png", replace 
		graph export "`file_path_in'/reg output/leverage_a.eps", replace
	
	** Coverage ratio for printing
	twoway line coverage_a year, ytitle("Coverage Ratio") xtitle("Year") graphregion(color(white)) xlabel(2000(2) 2015) saving("`file_path_in'/reg output/coverage_a", replace) 
		graph export "`file_path_in'/reg output/coverage_a.png", replace 
		graph export "`file_path_in'/reg output/coverage_a.eps", replace
		
restore

** quarterly vars
preserve
	collapse leverage_q coverage_q  , by(year)
	local listvar leverage_q coverage_q 
	label var leverage_q "leverage"
	label var leverage_q "coverage"
	foreach c of local listvar {
		local y `=subinstr("`c'","_q","",.)'
		twoway line `c' year, title("`y'") ytitle("") xtitle("year") graphregion(color(white)) xlabel(2000(2)2015) saving("`file_path_in'/reg output/`c'", replace) 
		graph export "`file_path_in'/reg output/`c'.png", replace 
		graph export "`file_path_in'/reg output/`c'.eps", replace
	}	
	** Leverage ratio for printing
	twoway line leverage_q year, ytitle("Leverage Ratio") xtitle("Year") graphregion(color(white)) xlabel(2000(2) 2015) saving("`file_path_in'/reg output/leverage_q", replace) 
		graph export "`file_path_in'/reg output/leverage_q.png", replace 
		graph export "`file_path_in'/reg output/leverage_q.eps", replace

	** Coverage ratio for printing
	twoway line coverage_q year, ytitle("Coverage Ratio") xtitle("Year") graphregion(color(white)) xlabel(2000(2) 2015) saving("`file_path_in'/reg output/coverage_q", replace) 
		graph export "`file_path_in'/reg output/coverage_q.png", replace 
		graph export "`file_path_in'/reg output/coverage_q.eps", replace
		
restore


local varlist leverage coverage 
foreach c of local varlist {
	graph combine "`file_path_in'/reg output/`c'_a.gph" "`file_path_in'/reg output/`c'_q.gph" , graphregion(color(white))
	graph export "`file_path_in'/reg output/`c'_aq.eps", replace
	graph export "`file_path_in'/reg output/`c'_aq.png", replace
}
save "`file_path_in'/Compustat/builderfinq.dta", replace

collapse lots_owned_a lots_option_a  cash_a land_under_dev_a assets_a , by(year)
	
	label var lots_owned "Owned"
	label var lots_option "Optioned"
	replace lots_owned = lots_owned/10000
	replace lots_option=lots_option/10000
	
	label var assets_a "Assets $(Mil)"
	twoway line assets_a year, xtitle("Year") graphregion(color(white)) graphregion(color(white)) xlabel(2000(2)2015) saving("`file_path_in'/reg output/assets_a", replace) 
	graph export "`file_path_in'/reg output/assets_a.eps", replace
	graph export "`file_path_in'/reg output/assets_a.png", replace
	
	twoway line lots* year, xtitle("Year") ytitle("Lots (10,000)") graphregion(color(white)) graphregion(color(white)) lpattern(solid dash) xlabel(2000(2)2015) saving("`file_path_in'/reg output/lotsbyyr", replace) 
	graph export "`file_path_in'/reg output/lotsbyyr.eps", replace
	graph export "`file_path_in'/reg output/lotsbyyr.png", replace
	
	label var cash "Cash $(Mil)"
	twoway line cash year, xtitle("Year") graphregion(color(white)) graphregion(color(white))  xlabel(2000(2) 2015)  saving("`file_path_in'/reg output/cash", replace) 
	graph export "`file_path_in'/reg output/cash_a.eps", replace
	graph export "`file_path_in'/reg output/cash_a.png", replace
	
	label var land_under "Land Under Dev."
	twoway line land_under year, xtitle("Year") graphregion(color(white)) ytitle("Land Under Development $(Mil)") xlabel(2000(2)2015) saving("`file_path_in'/reg output/land_dev", replace) 
	graph export "`file_path_in'/reg output/land_dev.eps", replace
	graph export "`file_path_in'/reg output/land_dev.png", replace
	

** combine graphs
graph combine "`file_path_in'/reg output/land_dev.gph" "`file_path_in'/reg output/lotsbyyr.gph"   ///
	"`file_path_in'/reg output/leverage_a.gph" "`file_path_in'/reg output/coverage_a.gph" ,	graphregion(color(white)) 
graph export "`file_path_in'/reg output/fin_health_a.eps", replace
graph export "`file_path_in'/reg output/fin_health_a.png", replace

